﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Common;

namespace Enterprise.Data.Email
{
    /// <summary>
    /// 邮件
    /// </summary>
    public class EMailData
    {
        /// <summary>
        /// 获取邮件集合
        /// </summary>
        /// <param name="email"></param>
        /// <returns></returns>
        public DataSet GetMails(string email)
        {
            string sql = "select * from email_inbox where email ='" + email + "' and ISDELETE=0 order by EmailDate DESC";
            return Enterprise.DataFactory.DbExecute.GetDataSet(sql);
        }
        
        /// <summary>
        /// 未读邮件集合
        /// </summary>
        /// <param name="email"></param>
        /// <returns></returns>
        public int NotReadCount(int userid)
        {
            string sql = "select count(1) from email_inbox a ,email_settings b where a.email=b.email and a.isdelete=0 and b.usserid="+userid;
            return int.Parse(Enterprise.DataFactory.DbExecute.ExecuteScalar(sql).ToString());
        }

        /// <summary>
        /// 获取邮箱设置
        /// </summary>
        /// <param name="userId"></param>
        /// <returns></returns>
        public DataSet GetMailSetting(int userId)
        {
            string sql = "select t.* from email_settings t where userid="+userId;
            return Enterprise.DataFactory.DbExecute.GetDataSet(sql);
        }

        /// <summary>
        /// 收件箱是否有邮件
        /// </summary>
        /// <param name="email"></param>
        /// <returns></returns>
        public bool Exist(string email)
        {
            string sql = "select count(1) from email_inbox where isdelete=0 and email='"+email+"'";
            return Enterprise.DataFactory.DbExecute.Exist(sql);
        }        

        /// <summary>
        /// 新增邮箱设置
        /// </summary>
        /// <param name="_userid">用户ID</param>
        /// <param name="_email">邮箱</param>
        /// <param name="_pwd">密码</param>
        /// <param name="_popserver"></param>
        /// <param name="_pop_port"></param>
        /// <param name="_smtpserver"></param>
        /// <param name="_smtp_port"></param>
        /// <param name="_enable">接收正常1、接收不正常0</param>
        public void Insert(int _userid, string _email, string _pwd, string _popserver, int _pop_port, string _smtpserver, int _smtp_port, int _enable)
        {
            string sql = @"insert into EMAIL_SETTINGS (
            EMAIL,LOGINNAME,LOGINPASSWORD,SMTPSERVER,POPSERVER,SMTPPORT,POPPORT,ISENABLE,ISREADY,USERID)
            values ('{0}','{1}','{2}','{3}','{4}',{5},{6},{7},{8},{9})";
            sql = string.Format(sql,_email,_email.Split('@').First(),_pwd,_smtpserver,_popserver,_smtp_port,_pop_port,_enable,0,_userid);
            Enterprise.DataFactory.DbExecute.Execute(sql);
        }

        /// <summary>
        /// 根据USERID 更新邮箱设置 考虑到未来一个用户多个邮箱的扩展 数据表主键为USERID & EMAIL
        /// </summary>
        /// <param name="_userid"></param>
        /// <param name="_email"></param>
        /// <param name="_pwd"></param>
        /// <param name="_popserver"></param>
        /// <param name="_pop_port"></param>
        /// <param name="_smtpserver"></param>
        /// <param name="_smtp_port"></param>
        /// <param name="_enable"></param>
        public void Update(int _userid, string _email, string _pwd, string _popserver, int _pop_port, string _smtpserver, int _smtp_port, int _enable)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update EMAIL_SETTINGS set ");
            strSql.Append("EMAIL='{0}',");
			strSql.Append("LOGINNAME='{1}',");
			strSql.Append("LOGINPASSWORD='{2}',");
			strSql.Append("SMTPSERVER='{3}',");
			strSql.Append("POPSERVER='{4}',");
			strSql.Append("SMTPPORT={5},");
			strSql.Append("POPPORT={6},");
			strSql.Append("ISENABLE={7},");
			strSql.Append("ISREADY={8}");
			strSql.Append(" where USERID={9} ");
            string sql = string.Format(strSql.ToString(), _email, _email.Split('@').First(), _pwd, _smtpserver, _popserver, _smtp_port, _pop_port, _enable, 0, _userid);
            Enterprise.DataFactory.DbExecute.Execute(sql);
        }

        /// <summary>
        /// 删除邮件
        /// </summary>
        /// <param name="MessageIds"></param>
        public void Delete(List<string> MessageIds)
        {
            string ids = ForCollection(MessageIds);
            string sql = "update email_inbox set isdelete=1 where messageid in " + ids;
            Enterprise.DataFactory.DbExecute.Execute(sql);
        }

        private string ForCollection(List<string> MessageIds)
        {
            string ids = "('0',";
            foreach (string id in MessageIds)
            {
                ids += "'" + id + "',";
            }
            ids = ids.Remove(ids.Length - 1);
            ids += ")";
            return ids;
        }

        public DataSet getMailDetail(string messageId)
        {
            string sql = "select * from email_inbox where messageid='"+messageId+"'";
            return Enterprise.DataFactory.DbExecute.GetDataSet(sql);
        }

        public void SetReaded(List<string> MessageIds)
        {
            string ids = ForCollection(MessageIds);
            string sql = "update email_inbox set readed=1 where messageid in " + ids;
            Enterprise.DataFactory.DbExecute.Execute(sql); 
        }

        /// <summary>
        /// 邮箱是否正常
        /// </summary>
        /// <param name="email"></param>
        /// <returns></returns>
        public bool IsOk(string email)
        {
            string sql = "select count(1) from email_settings t where t.isenable=1 and t.email='"+email+"'";
            return Enterprise.DataFactory.DbExecute.Exist(sql);
        }

        /// <summary>
        /// 将邮件存入发件箱Email_outbox
        /// </summary>
        /// <param name="from"></param>
        /// <param name="to"></param>
        /// <param name="subject"></param>
        /// <param name="body"></param>
        /// <param name="attch"></param>
        /// <param name="success"></param>
        public void SendMail(string from, string to, string subject, string body, string attch, int success,string cc)
        {
            string sql = @"insert into Email_OutBox(Email,EmailTo,EmailSubject,EmailBody,EmailAttachments,SendSuccess,SendTime,EmailCC) values ('{0}','{1}','{2}','{3}','{4}',{5},to_date('{6}','yyyy-mm-dd hh24:mi:ss'),'{7}')";
            sql = string.Format(sql, from, to, subject, body, attch, success, DateTime.Now,cc);
            Enterprise.DataFactory.DbExecute.Execute(sql);
        }

        /// <summary>
        /// 获取发件箱邮件
        /// </summary>
        /// <param name="email"></param>
        /// <returns></returns>
        public DataSet getOutbox(string email)
        {
            string sql = "select * from email_outbox where email='"+email+"' order by sendtime desc";
            return Enterprise.DataFactory.DbExecute.GetDataSet(sql);
        }
    }
}
